﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
    private SqlConnection conn;
    private SqlDataAdapter adapter;

	public DataAccess()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public SqlConnection OpenConnection()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPDB"].ConnectionString);
        try
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Open();
            }
            else
            {
                conn.Open();
            }

        }
        catch
        {
            return null;
        }
        return conn;
    }


    public void CloseConnection(SqlConnection sqlCn)
    {
        if (sqlCn != null)
        {
            if (sqlCn.State == ConnectionState.Open)
                sqlCn.Close();
            sqlCn.Dispose();
        }
    }

    public DataTable executeSelect(string spName)
    {
        DataSet dataSet = new DataSet();
        //Open connection
        conn = OpenConnection();

        adapter = new SqlDataAdapter(spName, conn);
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

        adapter.Fill(dataSet);
        
        //Close connection
        CloseConnection(conn);
        return dataSet.Tables[0];

    }

    public DataTable executeSelect(string spName, SqlParameter[] paramList)
    {
        DataSet dataSet = new DataSet();
        //Open connection
        conn = OpenConnection();

        adapter = new SqlDataAdapter(spName, conn);
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        adapter.SelectCommand.Parameters.AddRange(paramList);

        adapter.Fill(dataSet);
        //Close connection
        CloseConnection(conn);

        return dataSet.Tables[0];

    }




    public int executeUpdate(string spName, SqlParameter[] paramList)
    {

        int result = 0;
        //Open connection
        conn = OpenConnection();

        SqlCommand command = new SqlCommand(spName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(paramList);

        try
        {
            result = command.ExecuteNonQuery();
            
        }
        catch (SqlException)
        {
            //relationship error
            result = 0;
        }
        //Close connection
        CloseConnection(conn);

        return result;
        
    }

  
}